package com.example.demo.mapper;

import com.example.demo.entity.VideoAllReply;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author 第五小组
 * @since 2021-08-04
 */

public interface VideoAllReplyMapper extends BaseMapper<VideoAllReply> {
    @Select("SELECT COUNT(*) as count  FROM video_all_reply  WHERE message_length<10 UNION ALL\n" +
            "            SELECT COUNT(*)  FROM video_all_reply  WHERE message_length<20 AND message_length >=10 UNION ALL\n" +
            "            SELECT COUNT(*)  FROM video_all_reply  WHERE message_length<30 AND message_length >=20 UNION ALL\n" +
            "            SELECT COUNT(*)  FROM video_all_reply  WHERE message_length<40 AND message_length >=30 UNION ALL\n" +
            "            SELECT COUNT(*)  FROM video_all_reply  WHERE message_length<50 AND message_length >=40 UNION ALL\n" +
            "\t\t\t\t\t  SELECT COUNT(*)  FROM video_all_reply  WHERE  message_length >=50")
    public List<String> reply_count() ;
/*某视频评论字数分布统计*/

    @Select("SELECT COUNT(*) as count  FROM video_all_reply  WHERE user_level=2 UNION ALL\n" +
            "                        SELECT COUNT(*)  FROM video_all_reply  WHERE user_level=3 UNION ALL \n" +
            "                        SELECT COUNT(*)  FROM video_all_reply  WHERE user_level=4  UNION ALL \n" +
            "                        SELECT COUNT(*)  FROM video_all_reply  WHERE user_level=5 UNION ALL \n" +
            "                        SELECT COUNT(*)  FROM video_all_reply  WHERE user_level=6  " )
    public List<String> level_count();

    @Select("SELECT COUNT(*) as count  FROM video_all_reply  WHERE user_sex='男' UNION ALL\n" +
            "                        SELECT COUNT(*)  FROM video_all_reply  WHERE user_sex='女' UNION ALL\n" +
            "                        SELECT COUNT(*)  FROM video_all_reply  WHERE user_sex='保密' ")
    public List<String> sex_count();

    @Select("SELECT COUNT(*) as count  FROM video_all_reply  WHERE vip>0 UNION ALL\n" +
            "                        SELECT COUNT(*)  FROM video_all_reply  WHERE vip=0\n")
    public List<String> vip_count();

    @Select("select count(*) as count , HOUR(reply_time) DIV 6 from video_all_reply group by HOUR(reply_time) DIV 6")
    public List<String> time_count();

    @Select("SELECT message FROM video_all_reply")
    public  List<String> word_cloud();
}
